{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b20f3d3-7fef-4a50-b371-6ce7d40d4f89",
   "metadata": {},
   "outputs": [],
   "source": [
    "from faker import Faker\n",
    "import pandas as pd\n",
    "import random\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# 初始化Faker，使用中文\n",
    "fake = Faker('zh_CN')\n",
    "Faker.seed(42)\n",
    "random.seed(42)\n",
    "\n",
    "# 定义服装相关的商品数据（使用服装类目）\n",
    "CLOTHING_PRODUCTS = [\n",
    "    {'name': '纯棉圆领T恤', 'spu_prefix': 'TS', 'price_range': (89, 299)},\n",
    "    {'name': '修身牛仔裤', 'spu_prefix': 'JP', 'price_range': (199, 599)},\n",
    "    {'name': '连帽卫衣', 'spu_prefix': 'HD', 'price_range': (159, 459)},\n",
    "    {'name': '休闲衬衫', 'spu_prefix': 'SH', 'price_range': (129, 399)},\n",
    "    {'name': '运动裤', 'spu_prefix': 'SP', 'price_range': (99, 359)},\n",
    "    {'name': '羽绒服', 'spu_prefix': 'DJ', 'price_range': (399, 1299)},\n",
    "    {'name': '针织衫', 'spu_prefix': 'KN', 'price_range': (149, 499)},\n",
    "    {'name': '西装外套', 'spu_prefix': 'BZ', 'price_range': (399, 999)},\n",
    "    {'name': '休闲裤', 'spu_prefix': 'CP', 'price_range': (139, 449)},\n",
    "    {'name': '连衣裙', 'spu_prefix': 'DR', 'price_range': (179, 699)},\n",
    "]\n",
    "\n",
    "COLORS = ['黑色', '白色', '蓝色', '灰色', '红色', '绿色', '黄色', '紫色', '粉色', '米色']\n",
    "SIZES = ['XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL']\n",
    "STORE_NAMES = ['时尚潮流旗舰店', '优品服饰专营店', '都市风尚官方店', '经典服装品牌店', '时代服饰旗舰店']\n",
    "PLATFORMS = ['淘宝', '天猫', '京东', '拼多多', '抖音']\n",
    "STATUSES = ['已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '待发货', '已发货', '已取消']\n",
    "SUB_ORDER_STATUSES = ['已完成', '已完成', '已完成', '已完成', '已发货', '待发货']\n",
    "REFUND_STATUSES = ['无退款', '无退款', '无退款', '无退款', '无退款', '无退款', '无退款', '无退款', '部分退款', '全额退款']\n",
    "IS_GIFT = ['否', '否', '否', '否', '否', '否', '否', '否', '否', '是']\n",
    "\n",
    "# 生成固定的用户ID池（200个用户，可以重复购买）\n",
    "USER_IDS = [f'U{str(i).zfill(8)}' for i in range(1, 201)]\n",
    "\n",
    "def generate_order_data(num_records=1000):\n",
    "    \"\"\"生成订单数据\"\"\"\n",
    "    data = []\n",
    "    \n",
    "    for i in range(1, num_records + 1):\n",
    "        # 选择商品\n",
    "        product = random.choice(CLOTHING_PRODUCTS)\n",
    "        color = random.choice(COLORS)\n",
    "        size = random.choice(SIZES)\n",
    "        \n",
    "        # 生成订单号\n",
    "        internal_order_number = f'IO{datetime.now().strftime(\"%Y%m%d\")}{str(i).zfill(6)}'\n",
    "        online_order_number = f'ON{fake.random_number(digits=15)}'\n",
    "        sub_order_number = f'SO{fake.random_number(digits=15)}'\n",
    "        online_sub_order_number = f'OSO{fake.random_number(digits=15)}'\n",
    "        original_online_order_number = online_order_number\n",
    "        \n",
    "        # 生成SPU和SKU\n",
    "        spu = f'{product[\"spu_prefix\"]}{fake.random_number(digits=6)}'\n",
    "        sku = f'{spu}-{color[:1]}-{size}'\n",
    "        \n",
    "        # 生成时间（最近一年内）\n",
    "        order_time = fake.date_time_between(start_date='-1y', end_date='now')\n",
    "        payment_date = order_time + timedelta(minutes=random.randint(1, 120))\n",
    "        \n",
    "        # 发货日期：付款后1-3天内发货\n",
    "        shipping_date = payment_date + timedelta(days=random.randint(1, 3))\n",
    "        \n",
    "        # 价格相关\n",
    "        original_price = round(random.uniform(product['price_range'][0], product['price_range'][1]), 2)\n",
    "        unit_price = round(original_price * random.uniform(0.6, 1.0), 2)  # 可能有折扣\n",
    "        quantity = random.choices([1, 2, 3, 4, 5], weights=[60, 20, 10, 7, 3])[0]\n",
    "        product_amount = round(unit_price * quantity, 2)\n",
    "        \n",
    "        # 应付金额和已付金额（考虑运费等）\n",
    "        shipping_fee = random.choice([0, 0, 0, 5, 10, 15])\n",
    "        payable_amount = round(product_amount + shipping_fee, 2)\n",
    "        paid_amount = payable_amount\n",
    "        \n",
    "        # 地址信息\n",
    "        province = fake.province()\n",
    "        city = fake.city()\n",
    "        \n",
    "        # 退款相关\n",
    "        refund_status = random.choice(REFUND_STATUSES)\n",
    "        if refund_status == '无退款':\n",
    "            registered_quantity = 0\n",
    "            actual_refund_quantity = 0\n",
    "        elif refund_status == '部分退款':\n",
    "            registered_quantity = random.randint(1, quantity)\n",
    "            actual_refund_quantity = registered_quantity\n",
    "        else:  # 全额退款\n",
    "            registered_quantity = quantity\n",
    "            actual_refund_quantity = quantity\n",
    "        \n",
    "        # 构建数据行\n",
    "        record = {\n",
    "            'id': i,\n",
    "            'internal_order_number': internal_order_number,\n",
    "            'online_order_number': online_order_number,\n",
    "            'store_name': random.choice(STORE_NAMES),\n",
    "            'full_channel_user_id': random.choice(USER_IDS),  # 从用户池中随机选择\n",
    "            'shipping_date': shipping_date,\n",
    "            'payment_date': payment_date,\n",
    "            'payable_amount': payable_amount,\n",
    "            'paid_amount': paid_amount,\n",
    "            'status': random.choice(STATUSES),\n",
    "            'consignee': fake.name(),\n",
    "            'spu': spu,\n",
    "            'order_time': order_time,\n",
    "            'province': province,\n",
    "            'city': city,\n",
    "            'platform': random.choice(PLATFORMS),\n",
    "            'sub_order_number': sub_order_number,\n",
    "            'online_sub_order_number': online_sub_order_number,\n",
    "            'original_online_order_number': original_online_order_number,\n",
    "            'sku': sku,\n",
    "            'quantity': quantity,\n",
    "            'unit_price': unit_price,\n",
    "            'product_name': product['name'],\n",
    "            'color_and_spec': f'{color}/{size}',\n",
    "            'product_amount': product_amount,\n",
    "            'original_price': original_price,\n",
    "            'is_gift': random.choice(IS_GIFT),\n",
    "            'sub_order_status': random.choice(SUB_ORDER_STATUSES),\n",
    "            'refund_status': refund_status,\n",
    "            'registered_quantity': registered_quantity,\n",
    "            'actual_refund_quantity': actual_refund_quantity,\n",
    "        }\n",
    "        \n",
    "        data.append(record)\n",
    "    \n",
    "    return data\n",
    "\n",
    "def main():\n",
    "    \"\"\"主函数\"\"\"\n",
    "    print(\"开始生成ERP订单数据...\")\n",
    "    \n",
    "    # 生成数据\n",
    "    orders = generate_order_data(1000)\n",
    "    \n",
    "    # 转换为DataFrame\n",
    "    df = pd.DataFrame(orders)\n",
    "    \n",
    "    # 确保所有字段都没有空值\n",
    "    print(f\"\\n数据生成完成，共 {len(df)} 条记录\")\n",
    "    print(f\"\\n空值检查：\")\n",
    "    null_counts = df.isnull().sum()\n",
    "    if null_counts.sum() == 0:\n",
    "        print(\"✓ 所有字段均无空值\")\n",
    "    else:\n",
    "        print(null_counts[null_counts > 0])\n",
    "    \n",
    "    # 显示用户ID重复情况\n",
    "    user_counts = df['full_channel_user_id'].value_counts()\n",
    "    print(f\"\\n用户购买统计：\")\n",
    "    print(f\"  - 唯一用户数: {df['full_channel_user_id'].nunique()}\")\n",
    "    print(f\"  - 平均每用户订单数: {len(df) / df['full_channel_user_id'].nunique():.2f}\")\n",
    "    print(f\"  - 最多购买用户订单数: {user_counts.max()}\")\n",
    "    \n",
    "    # 保存到Excel\n",
    "    output_file = '数据.xlsx'\n",
    "    df.to_excel(output_file, index=False, engine='openpyxl')\n",
    "    print(f\"\\n数据已保存到: {output_file}\")\n",
    "    \n",
    "    # 显示数据预览\n",
    "    print(f\"\\n数据预览（前5行）：\")\n",
    "    print(df.head())\n",
    "    \n",
    "    print(f\"\\n商品分布：\")\n",
    "    print(df['product_name'].value_counts())\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    main()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
